forum

home / developersection / forums / how will data export to excel in datagridview

Ask Question

how will data export to excel in datagridview

Kenny Tangnde 9267 21-Sep-2011
Hi all,
   I have two question,First:Is how will data export to excel in datagridview.Second:
Is how will data import  to database in excel.
Thanks in advance.
c# c# 
Updated on 22-Aug-2013
Kenny Tangnde

Other

Follow Message
Can you answer this question?
Answer

17 Answers

Ely Sanders

Ely Sanders

22-Aug-2013

Hi, here is an alternative and for me a more flexible and simplier way you can try to export DataGridView to Excel file in C#:

private void btnExportToExcel_Click(object sender, EventArgs e)
{
    // Create DataTable for Excel file.
    var dataTable = ((DataTable)dataGridView1.DataSource).Copy();
    // Remove rows that do not meat the condition.
    for (int i = 0; i < dataTable.Rows.Count; i++)
        if (Convert.ToBoolean(dataTable.Rows[i][0]) == false)
            dataTable.Rows.Remove(dataTable.Rows[i]);
    // Remove unwanted column.
    dataTable.Columns.Remove(dataTable.Columns[0]);
    // Create new excel file.
    var excelFile = new ExcelFile();
    // Create new excel worksheet.
    var excelSheet = excelFile.Worksheets.Add(dataTable.TableName);
    // Insert data from dataTable to the worksheet starting at cell "A1".
    excelSheet.InsertDataTable(dataTable, new InsertDataTableOptions("A1") { ColumnHeaders = true });
    // Save as XLSX file.
    excelFile.Save("New File.xlsx");
}

Also here is how you can import Excel file to a DataTable in C#:

private void btnImportExcelToDB_Click(object sender, EventArgs e)
{
    var dataSet = new DataSet();
    // Load excel file.
    var excelFile = ExcelFile.Load("New File.xlsx");
    var excelWorksheet = excelFile.Worksheets.ActiveWorksheet;
    // Extract the data from the worksheet to newly created DataTable starting at 
    // first row and first column until the first empty row appears.
    var dataTable = excelWorksheet.CreateDataTable(new CreateDataTableOptions()
    {
        StartRow = 0,
        StartColumn = 0,
        ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow
    });
    dataTable.TableName = excelWorksheet.Name;
    // Add DataTable to DataSet.
    dataSet.Tables.Add(dataTable);
    // TODO import dataSet to Database
}

I used this C# Excel component.

Anonymous User

Anonymous User

03-Oct-2011
Hi Aken..

You can use following link to export data from excel to data-grid view,

http://support.microsoft.com/kb/321686

Thanks,
James Smith

James Smith

01-Oct-2011
Hi all..
This is really good.
Can any one give me some example of datagridview demo in c#. So i can make a complete program on it.

Thanks,
John Smith

John Smith

27-Sep-2011
Yes really good one...
Amit Singh

Amit Singh

27-Sep-2011
Thanks Rohit this code work for me.
Kenny Tangnde

Kenny Tangnde

25-Sep-2011

Hi all,
thanks Awadhendra Tiwari,It helps me a lot. 

 

Thanks.

Anonymous User

Anonymous User

24-Sep-2011
Thanks Awadhendra for the links. It helped me.
Anonymous User

Anonymous User

24-Sep-2011
Hi Aken H,
You can take a look at following link for learning linq

http://msdn.microsoft.com/en-us/vcsharp/aa336766
http://geekswithblogs.net/elroydsilva/archive/2008/11/03/learning-linq---an-overview.aspx

http://www.linqlearning.com/sp/16295-LINQ-to-SQL-Tutorials-Learning-Resources/

Thanks.
Kenny Tangnde

Kenny Tangnde

23-Sep-2011

hi Awadhendra Tiwari,Rohit Kesharwani,

      Thank you for your help,Thanks Rohit Kesharwani . he helps me a lot.

again  ask a question:

you can provide LINQ learning resources to me,and LINQ in project the integrated application.

Grateful.

 

John Smith

John Smith

23-Sep-2011
You got it aken :)
Uttam Misra

Uttam Misra

23-Sep-2011
use namespace:
using System.Data.SqlClient;
Anonymous User

Anonymous User

23-Sep-2011
Thanks Rohit. It helps me a lot.
Anonymous User

Anonymous User

23-Sep-2011
Hi aken H..
You can make following modification on above code

using System.Data.SqlClient;  //import this namespace at top

then replace OleDbCommand by SqlCommand class.

Then above code also work for you.

Thanks.
Kenny Tangnde

Kenny Tangnde

22-Sep-2011

hi Rohit Kesharwani,

  i not used using System.Data.OleDb; i want used using System.Data.SqlClient;

  Thank you always help me, I hereby express my sincere thank you.

Chris Anderson

Chris Anderson

22-Sep-2011

hi,

If you want that only checked rows will be exported then simply manipulate the above code:

List<DataGridViewRow> rows_with_checked_column = new List<DataGridViewRow>();
            foreach (DataGridViewRow rows in dataGridView1.Rows)
            {
                if (Convert.ToBoolean(rows.Cells[0].Value) == true)
                {
                    oledbCon.Open();
                    OleDbCommand cmd = new OleDbCommand("insert into [" + row["TABLE_NAME"].ToString() + "] values ('" + rows.Cells[1].Value.ToString() + "','" + rows.Cells[2].Value.ToString() + "')", oledbCon);
                    cmd.ExecuteNonQuery();
                    oledbCon.Close();
                }
            }

 

Thanks.

Kenny Tangnde

Kenny Tangnde

22-Sep-2011

hi Rohit Kesharwani,

private void btnExportToExcel_Click(object sender, EventArgs e)
   
 {
       
    List<DataGridViewRow> rows_with_checked_column=new List<DataGridViewRow>();
                foreach (DataGridViewRow row in dgv.Rows)
  
        {
   
           if (Convert.ToBoolean(row.Cells[0].Value) == true)
 
           //if value is true then start export to excel.
           //how to write code?
 
        {
                                 
  }
Chris Anderson

Chris Anderson

22-Sep-2011
hello aken,
Here are the solution of your both the problem:

Export Data from Excel in dataGridView:

Write the below code on the button click or on form load function:

using System.Data.OleDb;
OleDbConnection oledbCon = null;
 DataTable dt = null;
string csExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\\rohit\\File.xlsx;Extended Properties=\"Excel 8.0;HDR=Yes;\" ";
oledbCon = new OleDbConnection(csExcel);
 oledbCon.Open();
 dt = oledbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
oledbCon.Close();
String[] excelSheets = new String[dt.Rows.Count];
DataRow row = dt.Rows[0];
oledbCon.Open();
 OleDbDataAdapter oledbAdapter = new OleDbDataAdapter("select * from [" + row["TABLE_NAME"].ToString() + "]", oledbCon);
oledbCon.Close();
DataSet excelDataSet = new DataSet();
oledbAdapter.Fill(excelDataSet, "student");
DataTable excelTable = excelDataSet.Tables["student"];
dataGridView1.DataSource = excelTable;


Import Data from dataGridView in Excel:

using System.Data.OleDb;
OleDbConnection oledbCon = null;
DataTable dt = null;
string csExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\\rohit\\File.xlsx;Extended Properties=\"Excel 8.0;HDR=Yes;\" ";
oledbCon = new OleDbConnection(csExcel);
oledbCon.Open();
dt = oledbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
oledbCon.Close();
String[] excelSheets = new String[dt.Rows.Count];
DataRow row = dt.Rows[0];
 oledbCon.Open();
 for (int i = 0; i < dataGridView1.Rows.Count-1; i++)
{
  OleDbCommand cmd = new OleDbCommand("insert into [" + row["TABLE_NAME"].ToString() + "] values ('" +         
  dataGridView1.Rows[i].Cells[0].Value.ToString() + "','" + dataGridView1.Rows[i].Cells[1].Value.ToString() + "')",  
   oledbCon);
 cmd.ExecuteNonQuery();
 }
oledbCon.Close();


Happy Coding.
MindStick Training
MindStick Training

Liked By

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy